if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[custom_ZipSelectByCityState]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[custom_ZipSelectByCityState]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[custom_ZipSelectByCityState]
(
	@City varchar(100),
	@State varchar(100)
)
AS

SET NOCOUNT ON;

SELECT DISTINCT  [Zip].*
FROM 
	[Zip] INNER JOIN [STATE] ON [ZIP].stateid = [STATE].stateid
	INNER JOIN [CITYZIP] ON [ZIP].zipid = [CITYZIP].zipid
	INNER JOIN [CITY] ON [CITYZIP].cityid = [CITY].cityid
WHERE
	([CITY].[name] = @City) AND
	(([STATE].[name] = @State) OR ([STATE].[abbr] = @State))

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

